﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Project_ScansiaPacific
{
    public partial class FrNghiemThuGo : DevComponents.DotNetBar.Office2007Form
    {
        public FrNghiemThuGo()
        {
            InitializeComponent();
        }
        private string child=" - ",MaNCC1, LoaiGo1, NguoiNT1, Ngay1, Go1, PhanLoai1, ADorKD1, Day1, Rong1, Dai1, SLMau1, SLKD1, Giac2Mat1, MGiacDR1, MoMop1, Lem1, MucMoc1, Day1mm1, Rong2mm1, Dai20mm1, AmDo1, Lem1canh1, Cong1, TetDau1, Giac1Mat1, MotmatDR1, MatSongChet1, Khac1;
        private void combo_load()
        {
            Data dta = new Data();
            FormProcess Frp = new FormProcess();
            string sql_load = @"select MaNCC,TenNCC from NhaCungCap";
            Frp.cb1(cb_NhaCC, sql_load, 1, 0);

        }
        public void checktext_number(TextBox txt)
        {
            try
            {
                if (txt.Text.Length > 0)
                {
                    txt.Text = double.Parse(txt.Text).ToString();//ép kiểu float,nếu xảy ra ngoại lệ thì bẫy lỗi ở hàm checktext_number
                    txt.SelectionStart = txt.Text.Length;
                }
            }
            catch (Exception ex)//Bẫy lỗi khi nhập chữ vào text box
            {
                string s = "Input string";
                if (ex.Message.ToString().Contains(s))
                {
                    MessageBox.Show("Giá trị nhập phải là số");
                    txt.Text = "";
                }
                else
                    MessageBox.Show(ex.Message);
            }
        }

        private void txt_Day_TextChanged(object sender, EventArgs e)
        {
            checktext_number(txt_Day);
        }

        private void txt_Rong_TextChanged(object sender, EventArgs e)
        {
            checktext_number(txt_Rong);
        }

        private void txt_Dai_TextChanged(object sender, EventArgs e)
        {
            checktext_number(txt_Dai);
        }

        private void txt_SLTT_TextChanged(object sender, EventArgs e)
        {
            FormProcess Frp = new FormProcess();
            Frp.checktext_number(txt_SLTT);
        }

        private void txt_SLMau_TextChanged(object sender, EventArgs e)
        {
            checktext_number(txt_SLMau);
        }

        private void txt_SLKD_TextChanged(object sender, EventArgs e)
        {
            checktext_number(txt_SLKD);
        }

        private void FrNghiemThuGo_Load(object sender, EventArgs e)
        {
            combo_load();
        }

        private void txt_Giac2Mat_TextChanged(object sender, EventArgs e)
        {
            checktext_number(txt_Giac2Mat);
        }

        private void txt_MGvsDR_TextChanged(object sender, EventArgs e)
        {
            checktext_number(txt_MGvsDR);
        }

        private void txt_MoMop_TextChanged(object sender, EventArgs e)
        {
            checktext_number(txt_MoMop);
        }

        private void txt_Lem2Canh_TextChanged(object sender, EventArgs e)
        {
            checktext_number(txt_Lem2Canh);
        }

        private void txt_MucMoc_TextChanged(object sender, EventArgs e)
        {
            checktext_number(txt_MucMoc);
        }

        private void txt_Day1mm_TextChanged(object sender, EventArgs e)
        {
            checktext_number(txt_Day1mm);
        }

        private void txt_Rong1mm_TextChanged(object sender, EventArgs e)
        {
            checktext_number(txt_Rong2mm);
        }

        private void txt_Dai1mm_TextChanged(object sender, EventArgs e)
        {
            checktext_number(txt_Dai20mm);
        }

        private void txt_AmDo_TextChanged(object sender, EventArgs e)
        {
            checktext_number(txt_AmDo);
        }

        private void txt_Lem1Canh_TextChanged(object sender, EventArgs e)
        {
            checktext_number(txt_Lem1Canh);
        }

        private void txt_Cong20_TextChanged(object sender, EventArgs e)
        {
            checktext_number(txt_Cong20);
        }

        private void txt_TetDau_TextChanged(object sender, EventArgs e)
        {
            checktext_number(txt_TetDau);
        }

        private void txt_Giac1Mat_TextChanged(object sender, EventArgs e)
        {
            checktext_number(txt_Giac1Mat);
        }

        private void txt_MotMatDR_TextChanged(object sender, EventArgs e)
        {
            checktext_number(txt_MotMatDR);
        }

        private void txt_MatSongChet_TextChanged(object sender, EventArgs e)
        {
            checktext_number(txt_MatSongChet);
        }

        private void txt_Khac_TextChanged(object sender, EventArgs e)
        {
            checktext_number(txt_Khac);
        }
        private void save()
        {
            Process prs=new Process();
            SqlConnection Conn = prs.Connect();
            SqlCommand cmd = new SqlCommand();
            //string CanDau, BeCanh, CanMop, ChaNham, ChayDau, CuaRoulo, DinhViSai, DongNut, DuongGhep, DuongVat, GoKT, Keo, KheNan, Lem, LocXuoc, MeDauNan, NanSanSui, NutTetDau, SietOc, ThieuDau, TramTret, TrayDau, MauKhongDeu, Ngay1;
            //string DuongGhepCT, DuongGhepHo, MatKoDung, CongVan, DuongRuot, MucMoc, Giac, KeoGhep, ThieuQC, DoAm, MatSong, DinhViSaiKT, DinhViSaiMat, DuongVatBD, DuongVatBoCanh, KeoRapDinh, HoMong, NanSaiMat, NanKoDungTC, CaoTet, NanCaoThap, BaChan, Tan, SaiMau;
            double SLTT1 = 0;
            if (cb_NhaCC.Text == "" || txt_Ngay.Text == "" || txt_SLTT.Text == "" || txt_SLMau.Text== "" || txt_Rong.Text == "" || txt_Dai.Text == "" || txt_Day.Text == "")
            {
                MessageBox.Show("Nhập thiếu thông tin, vui lòng kiểm tra lại");
            }
            else
            {
                try
                {
                    Conn.Open();
                    cmd.Connection = Conn;
                    MaNCC1 = prs.substring_right(cb_NhaCC.Text, child);
                    if (MaNCC1 == "")
                        MessageBox.Show("Nhập sai Nhà Cung Cấp");
                    else
                    {
                        Ngay1 = txt_Ngay.Text.Trim();
                        string[] temp = Ngay1.Split('/');
                        Ngay1 = temp[1] + "/" + temp[0] + "/" + temp[2];
                        SLTT1 = double.Parse(txt_SLTT.Text);
                        LoaiGo1 = txt_LoaiGo.Text;
                        NguoiNT1 = txt_NguoiNT.Text;
                        Go1 = txt_Go.Text;
                        PhanLoai1 = txt_PhanLoai.Text;
                        ADorKD1 = txt_ADKD.Text;
                        Day1 = txt_Day.Text;
                        Rong1 = txt_Rong.Text;
                        Dai1 = txt_Dai.Text;
                        if (txt_SLMau.Text == "")
                            SLMau1 = "0";
                        else
                            SLMau1 = txt_SLMau.Text;
                        if (txt_SLKD.Text == "")
                            SLKD1 = "0";
                        else
                            SLKD1 = txt_SLKD.Text;
                        if (txt_Giac2Mat.Text == "")
                            Giac2Mat1 = "0";
                        else
                            Giac2Mat1 = txt_Giac2Mat.Text;
                        if (txt_MGvsDR.Text == "")
                            MGiacDR1 = "0";
                        else
                            MGiacDR1 = txt_MGvsDR.Text;
                        if (txt_MoMop.Text == "")
                            MoMop1 = "0";
                        else
                            MoMop1 = txt_MoMop.Text;
                        if (txt_Lem2Canh.Text == "")
                            Lem1 = "0";
                        else
                            Lem1 = txt_Lem2Canh.Text;
                        if (txt_MucMoc.Text == "")
                            MucMoc1 = "0";
                        else
                            MucMoc1 = txt_MucMoc.Text;
                        if (txt_Day1mm.Text == "")
                            Day1mm1 = "0";
                        else
                            Day1mm1 = txt_Day1mm.Text;
                        if (txt_Rong2mm.Text == "")
                            Rong2mm1 = "0";
                        else
                            Rong2mm1 = txt_Rong2mm.Text;
                        if (txt_Dai20mm.Text == "")
                            Dai20mm1 = "0";
                        else
                            Dai20mm1 = txt_Dai20mm.Text;
                        if (txt_AmDo.Text == "")
                            AmDo1 = "0";
                        else
                            AmDo1 = txt_AmDo.Text;
                        if (txt_Lem1Canh.Text == "")
                            Lem1canh1 = "0";
                        else
                            Lem1canh1 = txt_Lem1Canh.Text;
                        if (txt_Cong20.Text == "")
                            Cong1 = "0";
                        else
                            Cong1 = txt_Cong20.Text;
                        if (txt_TetDau.Text == "")
                            TetDau1 = "0";
                        else
                            TetDau1 = txt_TetDau.Text;
                        if (txt_Giac1Mat.Text == "")
                            Giac1Mat1 = "0";
                        else
                            Giac1Mat1 = txt_Giac1Mat.Text;
                        if (txt_MotMatDR.Text == "")
                            MotmatDR1 = "0";
                        else
                            MotmatDR1 = txt_MotMatDR.Text;
                        if (txt_MatSongChet.Text == "")
                            MatSongChet1 = "0";
                        else
                            MatSongChet1 = txt_MatSongChet.Text;
                        if (txt_Khac.Text == "")
                            Khac1 = "0";
                        else
                            Khac1 = txt_Khac.Text;
                        string sqlinsert = @"INSERT INTO [dbo].[NghiemThuGo]([NhaCC],[LoaiGo],[NguoiNT],[Ngay],[GoDV],[PhanLoai],[ADorKD],[ChieuDay],[ChieuRong],[ChieuDai],[SLTT],[SLMau],[SLKD],[Giac2Mat],[MGiacMDR],[MoMop],[Lem2Canh],[MucMoc],[Day1mm],[Rong2mm],[Dai20mm],[AmDo],[Lem1Canh],[Cong20mm],[TetDau],[Giac1Mat],[MotmatDR],[MatSongChet],[Khac])VALUES(N'";
                        sqlinsert += MaNCC1 + "',N'" + LoaiGo1 + "',N'" + NguoiNT1 + "',N'" + Ngay1 + "',N'" + Go1 + "',N'" + PhanLoai1 + "',N'" + ADorKD1 + "'," + Day1 + "," + Rong1 + "," + Dai1 + "," + SLTT1 + "," + SLMau1 + "," + SLKD1 + "," + Giac2Mat1 + "," + MGiacDR1 + "," + MoMop1 + "," + Lem1 + "," + MucMoc1 + "," + Day1mm1 + "," + Rong2mm1 + "," + Dai20mm1 + "," + AmDo1 + "," + Lem1canh1 + "," + Cong1 + "," + TetDau1 + "," + Giac1Mat1 + "," + MotmatDR1 + "," + MatSongChet1 + "," + Khac1 + ")";
                        cmd.CommandText = sqlinsert;
                        //MessageBox.Show(sqlinsert);
                        cmd.ExecuteNonQuery();
                        MessageBox.Show("Dữ liệu được lưu thành công");
                        prs.reset_txt(groupPanel2);
                        txt_SLTT.Text = "";
                        txt_SLMau.Text = "";
                        txt_SLKD.Text = "";
                        txt_Day.Text = "";
                        txt_Rong.Text = "";
                        txt_Dai.Text = "";
                        cb_NhaCC.Focus();
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
           
        }

        private void FrNghiemThuGo_KeyDown(object sender, KeyEventArgs ex)
        {
            if (ex.KeyData == Keys.Enter)
            {
                SendKeys.Send("{TAB}");
            }
            if (ex.KeyCode == Keys.F11)
            {
                bt_save_Click(null, null);
            }
        }

        private void bt_save_Click(object sender, EventArgs e)
        {
            save();
        }
        private void ExportExcel(DataTable dt, string sheetName)
        {

            Microsoft.Office.Interop.Excel.Application ExcelObj = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(@"C:\\Huynh\\NTGo.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, false, Microsoft.Office.Interop.Excel.XlCorruptLoad.xlNormalLoad);
            Microsoft.Office.Interop.Excel.Sheets sheet = theWorkbook.Worksheets;
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheet.get_Item(1);
            //worksheet.Activate();
            object[,] arr = new object[dt.Rows.Count, dt.Columns.Count];
            //Chuyển dữ liệu từ DataTable vào mảng đối tượng
            for (int r = 0; r < dt.Rows.Count; r++)
            {
                DataRow dr = dt.Rows[r];
                for (int c = 0; c < dt.Columns.Count; c++)
                {
                    //DataRow dr = dt.Rows[r];
                    arr[r, c] = dr[c];
                }
            }

            //Thiết lập vùng điền dữ liệu
            int rowStart = 6;
            int columnStart = 1;

            int rowEnd = rowStart + dt.Rows.Count - 1;
            //int rowEnd = rowStart + dt.Columns.Count - 1;

            int columnEnd = columnStart + dt.Columns.Count - 1;
            //int columnEnd = dt.Rows.Count - 1 + columnStart;

            // Ô bắt đầu điền dữ liệu
            Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[rowStart, columnStart];
            // Ô kết thúc điền dữ liệu
            Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[rowEnd, columnEnd];
            // Lấy về vùng điền dữ liệu
            Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(c1, c2);

            //Điền dữ liệu vào vùng đã thiết lập
            range.Value2 = arr;
            //range.Columns.AutoFit();
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
            //theWorkbook=theWorkbook.Open("C:\\test.xlsx");
            //worksheet = ("sheet1");
            //range.Activate();
            object misValue = System.Reflection.Missing.Value;
            theWorkbook.Close(true, misValue, misValue);
            ExcelObj.Quit();

        }

        private void bt_exportexcel_Click(object sender, EventArgs e)
        {
            Process prs=new Process();
            string sql = @"select TenNCC,MaNCC,LoaiGo, NghiemThuGo.Ngay, GoDV, PhanLoai, ADorKD, ChieuDay, ChieuRong, ChieuDai, SLTT, SLMau, SLKD, Giac2Mat, MGiacMDR, MoMop, Lem2Canh, 
                      MucMoc, Day1mm, Rong2mm, Dai20mm, AmDo, Lem1Canh, Cong20mm, TetDau, Giac1Mat, MotmatDR, MatSongChet, Khac,Serial from dbo.NghiemThuGo inner join dbo.NhaCungCap on NhaCungCap.MaNCC=NghiemThuGo.NhaCC";
            //string sql = @"select MaNCC,TenNCC from dbo.NhaCungCap";
            DataSet ds = prs.dts(sql);
            ExportExcel(ds.Tables[0], "NghiemThuGo");
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                string Ngay_start1 = "03/13/2013", Ngay_End1 = "03/14/2013";

                Process prs = new Process();
                DataSet ds = new DataSet();
                SqlCommand cmd = new SqlCommand();
                SqlConnection Conn = prs.Connect();
                cmd.Connection = Conn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "Test";
                cmd.Parameters.AddWithValue("@flag", "1");
                cmd.Parameters.AddWithValue("@datebegin", Ngay_start1);
                cmd.Parameters.AddWithValue("@dateend", Ngay_End1);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);
                dataGridView1.DataSource = ds.Tables[0];
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
    }
}
